package com.otoe.dilidilivid.data.dao;


import android.util.Log;

import com.otoe.dilidilivid.data.entity.AddFavorites;
import com.otoe.dilidilivid.data.entity.Favorites;
import com.otoe.dilidilivid.data.operation.MysqlOperation;
import com.otoe.dilidilivid.data.entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

/**
 * author: yan
 * date: 2022.02.17
 **/
public class UserDao {

    private static final String TAG = "mysql-dilidiliDB-UserDao";

    /**
     * function: 登录
     */
    public int login(String userAccount, String userPassword) throws Exception {
        HashMap<String, Object> map = new HashMap<>();
        // 根据数据库名称，建立连接
        Connection connection = MysqlOperation.DBUtils.getConnect();
        int msg = 0;
        try {
            // mysql简单的查询语句。这里是根据user表的userid字段来查询某条记录
            String sql = "select * from USERS where U_name = ?";
            if (connection != null) {// connection不为null表示与数据库建立了连接
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null) {
                    Log.e(TAG, "账号：" + userAccount);
                    //根据账号进行查询
                    ps.setString(1, userAccount);
                    // 执行sql查询语句并返回结果集
                    ResultSet rs = ps.executeQuery();
                    int count = rs.getMetaData().getColumnCount();
                    //将查到的内容储存在map里
                    while (rs.next()) {
                        // 注意：下标是从1开始的
                        for (int i = 1; i <= count; i++) {
                            String field = rs.getMetaData().getColumnName(i);
                            map.put(field, rs.getString(field));
                        }
                    }
                    connection.close();
                    ps.close();

                    if (map.size() != 0) {
                        StringBuilder s = new StringBuilder();
                        //寻找密码是否匹配
                        for (String key : map.keySet()) {
                            if (key.equals("Password")) {
                                if (userPassword.equals(map.get(key))) {
                                    msg = 1;            //密码正确
                                } else
                                    msg = 2;            //密码错误
                                break;
                            }
                        }
                    } else {
                        Log.e(TAG, "查询结果为空");
                        msg = 3;
                    }
                } else {
                    msg = 0;
                }
            } else {
                msg = 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
            Log.d(TAG, "异常login：" + e.getMessage());
            msg = 0;
        }
        return msg;
    }


    /**
     * function: 注册
     */
    public boolean register(User user) throws Exception {
        // 根据数据库名称，建立连接
        Connection connection = MysqlOperation.DBUtils.getConnect();

        try {
            String sql = "insert into USERS(U_name,Password) values (?,?)";
            if (connection != null) {   // connection不为null表示与数据库建立了连接
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null) {

                    //将数据插入数据库

                    ps.setString(1, user.getU_name());
                    ps.setString(2, user.getPassword());

                    // 执行sql查询语句并返回结果集
                    int rs = ps.executeUpdate();
                    if (rs > 0)
                        return true;
                    else
                        return false;
                } else {
                    return false;
                }
            } else {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
            Log.e(TAG, "异常register：" + e.getMessage());
            return false;
        }
    }

    /**
     * function: 根据账号进行查找该用户是否存在
     */
    public User findUser(String uname) throws Exception {

        // 根据数据库名称，建立连接
        Connection connection = MysqlOperation.DBUtils.getConnect();
        User user = null;
        try {
            String sql = "select * from USERS where U_name = ?";
            if (connection != null) {// connection不为null表示与数据库建立了连接
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null) {
                    ps.setString(1, uname);
                    ResultSet rs = ps.executeQuery();

                    while (rs.next()) {
                        //注意：下标是从1开始
                        int userId = rs.getInt(1);
                        String U_name = rs.getString(2);
                        String password = rs.getString(3);
                        String state = rs.getString(4);
                        Date date = rs.getDate(5);
                        user = new User(userId, U_name, password, state, date);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            Log.d(TAG, "异常findUser：" + e.getMessage());
            return null;
        }
        return user;
    }

    //根据用户名获取它所有收藏夹
    public List<Favorites> unameToFid(String uname) {
        List<Favorites> Favorites = new ArrayList<>();
        try {
            Connection connection = MysqlOperation.DBUtils.getConnect();
            String sql = "select f.* from USERS u,ADD_FAVORITES a,FAVORITES f where U_name =? and u.U_id=a.U_id and a.F_id=f.F_id";
            if (connection != null) {
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null) {
                    ps.setString(1, uname);
                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        Favorites favorites1 = new Favorites();
                        favorites1.setF_id(rs.getInt(1));
                        favorites1.setF_name(rs.getString(2));
                        favorites1.setSize(rs.getInt(3));
                        favorites1.setComment(rs.getString(4));
                        favorites1.setVisible(rs.getBoolean(5));
                        Favorites.add(favorites1);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            return Favorites;
        }
    }
    //把视频加入收藏夹
    /*
    * 接口2（用户名，收藏夹id，视频id）{
 在收藏夹中查询收藏夹id
 if 没查到
       在Favourites中插入该收藏夹id，初始化size为1
 将视频id插入收藏夹-视频表中
}
    * */
    public void addToFavorite(String username,int F_id,int V_id){
        try {
            Connection connection = MysqlOperation.DBUtils.getConnect();
            if(connection!=null){
                String sql="select U_id from USERS where U_name=?";
                PreparedStatement ps=connection.prepareStatement(sql);
                int Uid=0;
                ResultSet rs;
                if(ps!=null){
                    rs=ps.executeQuery();
                    while(rs.next()){
                        Uid=rs.getInt(1);
                    }
                }

                sql="select * from FAVORITES f where F_id=?";
                ps=connection.prepareStatement(sql);
                if(ps!=null){

                    ps.setInt(1,F_id);
                    rs=ps.executeQuery();
                    //收藏夹为空
                    if(rs.getRow()==0){
                        sql="insert into FAVORITES (F_id,Size) values (?,1)";
                        ps=connection.prepareStatement(sql);
                        ps.execute();
                        sql="insert into ADD_FAVORITES (U_id,F_id) values(?,?)";
                        ps=connection.prepareStatement(sql);
                        ps.setInt(1,Uid);
                        ps.setInt(2,F_id);
                        ps.execute();
                    }
                    sql="insert into FAVORITES_LIST (F_id,V_id) values(?,?)";
                    ps= connection.prepareStatement(sql);
                    ps.setInt(1,F_id);
                    ps.setInt(2,V_id);
                    ps.execute();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List<User> selectAll(){
        List<User> userList=new ArrayList<>();
        try {
            Connection connection = MysqlOperation.DBUtils.getConnect();
            if(connection!=null){
                String sql="select * from USERS";
                PreparedStatement ps= connection.prepareStatement(sql);
                if(ps!=null){
                    ResultSet rs=ps.executeQuery();
                    while (rs.next()){
                        User user=new User();
                        user.setU_id(rs.getInt(1));
                        user.setU_name(rs.getString(2));
                        user.setPassword(rs.getString(3));
                        userList.add(user);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return userList;
    }

}

